package models

import (
	"database/sql"
	"fmt"

	"beego-api-demo/models/mymysql"

	"github.com/astaxie/beego"
	"github.com/go-sql-driver/mysql"
)

type Book struct {
	ID           int64  `json:"id,omitempty"`
	Title        string `json:"title"`
	Author       string `json:"author"`
	Publisher    string `json:"publisher"`
	Description  string `json:"description"`
	Isbn         string `json:"isbn"`
	SerialuNmber string `json:"serialuNmber"`
	State        int8   `json:"state"`
	Remark       string `json:"remark"`
	Cover        string `json:"cover"`
}

func NewBook(f *BookPostForm) *Book {
	book := Book{
		Title:        f.Title,
		Author:       f.Author,
		Publisher:    f.Publisher,
		Description:  f.Description,
		Isbn:         f.Isbn,
		SerialuNmber: f.SerialuNmber,
		State:        f.State,
		Remark:       f.Remark,
		Cover:        f.Cover}

	return &book
}

func (r *Book) Insert() (code int, err error) {
	db := mymysql.Conn()

	st, err := db.Prepare("INSERT INTO books(`title`, `author`, `publisher`, `description`, `isbn`, `serial_number`, `state`, `remark`, `cover`) VALUES(?, ?, ?, ?,?, ?, ?, ?, ?)")
	if err != nil {
		return ErrDatabase, err
	}
	defer st.Close()

	if _, err := st.Exec(r.Title, r.Author, r.Publisher, r.Description, r.Isbn, r.SerialuNmber, r.State, r.Remark, r.Cover); err != nil {
		if e, ok := err.(*mysql.MySQLError); ok {
			if e.Number == errNumberDuplicateKey {
				return ErrDupRows, err
			}
			return ErrDatabase, err
		}
		return ErrDatabase, err
	}

	return 0, nil
}

func (r *Book) FindByID(id int64) (code int, err error) {
	db := mymysql.Conn()

	st, err := db.Prepare("SELECT `id`, `title`, `author`, `publisher`, `description`, `isbn`, `serial_number`, `state`, `remark`, `cover` FROM books WHERE id = ?")
	if err != nil {
		return ErrDatabase, err
	}
	defer st.Close()

	row := st.QueryRow(id)

	if err := FillDataFromRow(row, r); err != nil {
		if err == sql.ErrNoRows {
			return ErrNotFound, err
		}
		return ErrDatabase, err
	}

	return 0, nil
}

// GetAllBooks query all matched records.
func GetAllBooks(queryVal map[string]string, queryOp map[string]string, order map[string]string, limit int64, offset int64) (records []Book, err error) {
	sqlStr := "SELECT `id`, `title`, `author`, `publisher`, `description`, `isbn`, `serial_number`, `state`, `remark`, `cover` FROM books"
	if len(queryVal) > 0 {
		sqlStr += " WHERE "
		first := true
		for k, v := range queryVal {
			if !first {
				sqlStr += " AND "
			} else {
				first = false
			}

			sqlStr += k
			sqlStr += " "
			sqlStr += queryOp[k]
			sqlStr += " '"
			sqlStr += v
			sqlStr += "'"
		}
	}
	if len(order) > 0 {
		sqlStr += " ORDER BY "
		first := true
		for k, v := range order {
			if !first {
				sqlStr += ", "
			} else {
				first = false
			}

			sqlStr += k
			sqlStr += " "
			sqlStr += v
		}
	}
	sqlStr += " LIMIT " + fmt.Sprintf("%d", limit)
	if offset > 0 {
		sqlStr += " OFFSET " + fmt.Sprintf("%d", offset)
	}
	beego.Debug("sqlStr:", sqlStr)

	db := mymysql.Conn()

	st, err := db.Prepare(sqlStr)
	if err != nil {
		return nil, err
	}
	defer st.Close()

	rows, err := st.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	records = make([]Book, 0, limit)
	for rows.Next() {
		r := Book{}
		if err := FillDataFromRows(rows, &r); err != nil {
			return nil, err
		}
		records = append(records, r)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return records, nil
}

func (r *Book) UpdateByID(id int64, f *BookPutForm) (code int, err error) {
	db := mymysql.Conn()

	st, err := db.Prepare("UPDATE books SET `title` = ?, `author` = ?, `publisher` = ?, `description` = ?, `isbn` = ?, `serial_number` = ?, `state` = ?, `remark` = ?, `cover` = ? WHERE id = ?")
	if err != nil {
		return ErrDatabase, err
	}
	defer st.Close()

	result, err := st.Exec(f.Title, f.Author, f.Publisher, f.Description, f.Isbn, f.SerialuNmber, f.State, f.Remark, f.Cover, id)
	if err != nil {
		return ErrDatabase, err
	}

	num, _ := result.RowsAffected()
	if num > 0 {
		return 0, nil
	}

	return ErrNotFound, nil
}

func (r *Book) DeleteByID(id int64) (code int, err error) {
	db := mymysql.Conn()

	st, err := db.Prepare("DELETE FROM books WHERE id = ?")
	if err != nil {
		return ErrDatabase, err
	}
	defer st.Close()

	result, err := st.Exec(id)
	if err != nil {
		return ErrDatabase, err
	}

	num, _ := result.RowsAffected()
	if num > 0 {
		return 0, nil
	}

	return ErrNotFound, nil
}

func FillDataFromRow(row *sql.Row, result *Book) (err error) {
	var tmpID sql.NullInt64
	var tmpTitle sql.NullString
	var tmpAuthor sql.NullString
	var tmpPublisher sql.NullString
	var tmpDescription sql.NullString
	var tmpIsbn sql.NullString
	var tmpSerialuNmberd sql.NullString
	var tmpState sql.NullInt32
	var tmpRemark sql.NullString
	var tmpCover sql.NullString

	err = row.Scan(&tmpID, &tmpTitle, &tmpAuthor, &tmpPublisher, &tmpDescription, &tmpIsbn, &tmpSerialuNmberd, &tmpState, &tmpRemark, &tmpCover)
	if err != nil {
		return err
	}

	if tmpID.Valid {
		result.ID = tmpID.Int64
	}
	if tmpTitle.Valid {
		result.Title = tmpTitle.String
	}
	if tmpAuthor.Valid {
		result.Author = tmpAuthor.String
	}
	if tmpPublisher.Valid {
		result.Publisher = tmpPublisher.String
	}
	if tmpDescription.Valid {
		result.Description = tmpDescription.String
	}
	if tmpIsbn.Valid {
		result.Isbn = tmpIsbn.String
	}
	if tmpSerialuNmberd.Valid {
		result.SerialuNmber = tmpSerialuNmberd.String
	}
	if tmpState.Valid {
		result.State = int8(tmpState.Int32)
	}
	if tmpRemark.Valid {
		result.Remark = tmpRemark.String
	}
	if tmpCover.Valid {
		result.Cover = tmpCover.String
	}
	return
}

func FillDataFromRows(row *sql.Rows, result *Book) (err error) {
	var tmpID sql.NullInt64
	var tmpTitle sql.NullString
	var tmpAuthor sql.NullString
	var tmpPublisher sql.NullString
	var tmpDescription sql.NullString
	var tmpIsbn sql.NullString
	var tmpSerialuNmberd sql.NullString
	var tmpState sql.NullInt32
	var tmpRemark sql.NullString
	var tmpCover sql.NullString

	err = row.Scan(&tmpID, &tmpTitle, &tmpAuthor, &tmpPublisher, &tmpDescription, &tmpIsbn, &tmpSerialuNmberd, &tmpState, &tmpRemark, &tmpCover)
	if err != nil {
		return err
	}

	if tmpID.Valid {
		result.ID = tmpID.Int64
	}
	if tmpTitle.Valid {
		result.Title = tmpTitle.String
	}
	if tmpAuthor.Valid {
		result.Author = tmpAuthor.String
	}
	if tmpPublisher.Valid {
		result.Publisher = tmpPublisher.String
	}
	if tmpDescription.Valid {
		result.Description = tmpDescription.String
	}
	if tmpIsbn.Valid {
		result.Isbn = tmpIsbn.String
	}
	if tmpSerialuNmberd.Valid {
		result.SerialuNmber = tmpSerialuNmberd.String
	}
	if tmpState.Valid {
		result.State = int8(tmpState.Int32)
	}
	if tmpRemark.Valid {
		result.Remark = tmpRemark.String
	}
	if tmpCover.Valid {
		result.Cover = tmpCover.String
	}
	return
}
